﻿/*
Commerce by Onex – eCommerce software platform (www.ecommerce.byonex.com)
Copyright (C) 2008-2010 by Onex Group

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using ShopModel;

namespace ShopDAO
{
    public class ProducerDAO
    {
        private string connectionString;

        public ProducerDAO()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["csShop"].ConnectionString;
        }

        private SqlConnection Connection()
        {
            return new SqlConnection(connectionString);
        }
        
        /// <summary>
        /// pobiera listę wszystkich producentów, zliczając produkty lub nie (Downloads list of all producers, counting products or not)
        /// </summary>
        /// <param name="GetProductsCount">Czy zliczać produkty dla producenta (Count products for producer?)</param>
        /// <param name="LanguageID">Id języka w jakim wyświetlana jest strona (Language ID in which page is displayed)</param>
        /// <returns>Lista obiektów typu producent przechowująca informacje o producentach (List of Producent objects storing information about producers)</returns>
        public List<Producer> GetProducers(bool GetProductsCount, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            List<Producer> producerList = new List<Producer>();
            Producer producer = null;

            try
            {
                sqlC.Open();
                SqlCommand com = sqlC.CreateCommand();
                com.CommandText = "GetProducers";
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add(new SqlParameter("@GetsProductsCount", GetProductsCount));
                com.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

                SqlDataReader sqlR = com.ExecuteReader();
                while (sqlR.Read())
                {
                    producer = new Producer();
                    producer.Name = sqlR["ProducerName"].ToString();
                    producer.IDproducer = (int)sqlR["IDproducer"];
                    if (GetProductsCount) producer.ProductsCount = (int)sqlR["count"];
                    producerList.Add(producer);
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }
            return producerList;
        }

        /// <summary>
        /// Zapisuje dane producenta w bazie (Saves producer data in the database)
        /// </summary>
        /// <param name="iDproducer">Unikalne id producenta (Unique producer id)</param>
        /// <param name="name">Nazwa producneta (Producer name)</param>
        /// <param name="LanguageID">Unikalne id języka w jakim wyświetlana jest strona (Unique language ID in which page is displayed)</param>
        /// <returns>Liczba zmodyfikowanych wierszy (Number of modified rows)</returns>
        public int SaveProducer(int iDproducer, string name, int LanguageID)
        {
            SqlConnection sqlC = Connection();
            int result = 0;
            try
            {
                sqlC.Open();
                SqlCommand com = sqlC.CreateCommand();
                com.CommandText = "SaveProducer";
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add(new SqlParameter("@ProducerName", name));
                com.Parameters.Add(new SqlParameter("@IDProducer", iDproducer));
                com.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

                if (!int.TryParse(com.ExecuteScalar().ToString(), out result))
                    result = 0;
                com.Parameters.Clear();
                sqlC.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }
            return result;
        }


        /// <summary>
        /// Usówa producenta z bazy (Deletes producer from the database)
        /// </summary>
        /// <param name="ID">Unikalen id producenta (Unique product id)</param>
        /// <returns>Zwraca 'true' jesli operacja zakończona powodzeniem (Returns ‘true’ is operation succeeded)</returns>
        public bool DeleteProducer(int ID)
        {
            SqlConnection sqlC = Connection();
            int result = 0;
            try
            {
                SqlCommand com = sqlC.CreateCommand();
                com.CommandText = "DeleteProducer";
                com.CommandType = CommandType.StoredProcedure;

                sqlC.Open();
                com.Parameters.Add(new SqlParameter("@IDproducer", ID));
                result = com.ExecuteNonQuery();
                com.Parameters.Clear();
                sqlC.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }
            if (result != 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// Sprawdza czy w bazie są produkty od wybranego producenta (Checks database to find products of chosen producer)
        /// </summary>
        /// <param name="ProducerId">Unikalen id producenta (Unique producer id)</param>
        /// <returns>Zwraca 'true' jeśli w bazie sa produkty od wybranego producenta (Returns ‘true’ if there are products of chosen producer in the database)</returns>
        public bool CheckIfProducerProductExist(int ProducerId)
        {
            SqlConnection sqlC = Connection();
            int result = 0;
            try
            {
                SqlCommand com = sqlC.CreateCommand();
                com.CommandText = "CheckIfProducerProductExist";
                com.CommandType = CommandType.StoredProcedure;

                sqlC.Open();

                com.Parameters.Add(new SqlParameter("@IDproducer", ProducerId));
                result = (int)com.ExecuteScalar();
                sqlC.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }
            if (result != 0)
                return true;
            else
                return false;

        }

        /// <summary>
        /// Pobiera producentów z kategoriami produktów (Downloads producers with products’ categories)
        /// </summary>
        /// <param name="LanguageID">Unikalne id języka w jakim wyświetlana jest strona (Unique language ID in which page is displayed)</param>
        /// <returns>Lista obiektów typu producent zawierająca informacje o producentach (List of Producer objects storing information about producers)</returns>
        public List<Producer> GetProducersWithCategories(int? LanguageID)
        {
            List<Producer> ListProducer = new List<Producer>();
            using (SqlConnection con = Connection())
            {
                con.Open();

                SqlCommand com = con.CreateCommand();
                com.CommandText = "GetProducersWithCategories";
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add("@LanguageID", SqlDbType.Int).Value = LanguageID;

                IDataReader dr = com.ExecuteReader();

                while (dr.Read())
                {
                    bool exists = false;
                    foreach (Producer p in ListProducer)
                    {
                        if (p.Name == dr["ProducerName"].ToString())
                        {
                            p.ProductsInCategories.Add((int)dr["IDCategory"]);
                            exists = true;
                            break;
                        }
                    }
                    if (!exists)
                    {
                        Producer producer = new Producer(dr["ProducerName"].ToString());
                        producer.IDproducer = (int)dr["IDProducer"];
                        producer.ProductsInCategories.Add((int)dr["IDCategory"]);
                        ListProducer.Add(producer);
                    }


                }
                return ListProducer;
            }
        }

        /// <summary>
        /// Pobiera producentów dla wybranej kategorii (Downloads producers for chosen category)
        /// </summary>
        /// <param name="_categoryId">Unikalne id categorii (Unique category id)</param>
        /// <param name="LanguageID">Unikalne id jezyka w jakim wyświetlana jest strona (Unique language ID in which page is displayed)</param>
        /// <returns>Lista obiektów typu producent zawierająca informacje o producentach (List of Producent objects storing information about producers)</returns>
        public List<Producer> GetProducersByCategory(int _categoryId, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            List<Producer> producerList = new List<Producer>();
            Producer producer = null;
            try
            {

                SqlCommand com = sqlC.CreateCommand();
                com.CommandText = "GetProducersByCategory";
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add(new SqlParameter("@CategoryID", _categoryId));
                com.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));
  

                sqlC.Open();
                SqlDataReader sqlR = com.ExecuteReader();
                while (sqlR.Read())
                {
                    producer = new Producer();
                    producer.Name = sqlR["ProducerName"].ToString();
                    producer.IDproducer = (int)sqlR["IDproducer"];
                    producerList.Add(producer);
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }
            return producerList;
        }
    }
}
